<?

/**
* Global fetchers functions
*
* @author Adesanto Asman
* @version 1.0
* @license Free
* @package GlobalFetchers
*
*/

class GlobalFetchers{

  /**
   * list of model with criteria
   *
   * @param unknown_type $Db
   * @param unknown_type $type
   * @param unknown_type $srcDb
   * @param unknown_type $nModel
   * @param unknown_type $lstModel
   */
  function lstModel(&$Db,
                     $type,
                     $srcDb,
                    &$nModel,
                    &$lstModel){

    switch($type){

      case _MODEL_TYPE_RAKITAN:
      case _MODEL_TYPE_PRODUKSI:
      case _MODEL_TYPE_NORMAL:

        $condt = " WHERE TYPE=".$type ;
        break;

      default:

        $condt = " ";
    }

    $query	= "SELECT *
               FROM ".$srcDb."
               ".$condt."
               ORDER BY MODEL ASC";

    $Db->execFetchQuery($query,
                        $nModel,
                        $lstModel);
  }

  /**
   * model name by model id
   *
   * @param Database  $Db
   * @param string    $modelId
   * @param string    $srcDb
   * @param string    $modelName
   */
  function modelName(&$Db,
                     &$modelId,
                      $srcDb,
                     &$modelName){

    $query	= "SELECT MODEL
               FROM ".$srcDb."
               WHERE	MODEL_ID='".$modelId."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $modelName = $result["MODEL"][0];
  }

  /**
   * quantity of composition
   *
   * @param Database  $Db
   * @param string    $modelId
   * @param string    $composModelId
   * @param string    $srcDb
   * @param integer   $qtyOfComposition
   */
  function qtyOfComposition(&$Db,
                            &$modelId,
                            &$composModelId,
                             $srcDb,
                            &$qtyOfComposition){

    $query	= "SELECT QTY
               FROM ".$srcDb."
               WHERE	MODEL_ID='".$modelId."'
                      AND COMPOS_MODEL_ID='".$composModelId."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $qtyOfComposition = $result["QTY"][0];
  }

  /**
   * if model is composited
   * return how many model
   *
   * @param Database  $Db
   * @param string    $modelId
   * @param string    $srcDb
   * @param integer   $nComposited
   * @param array     $lstComposited
   */
  function isModelComposited(&$Db,
                             &$modelId,
                              $srcDb,
                             &$nComposited,
                             &$lstComposited){

    $query	= "SELECT COMPOS_MODEL_ID
               FROM ".$srcDb."
               WHERE	MODEL_ID='".$modelId."' ";

    $Db->execFetchQuery($query,
                        $nComposited,
                        $result);

    $lstComposited = $result["COMPOS_MODEL_ID"];
  }

  /**
  * Fetch if this model id is serNumber active
  *
  * @param  string  $Db
  * @param  integer $modelId
  * @param  string  $srcDb
  * @param  integer $isSerNumber
  *
  */
  function isModelSerNumber(&$Db,
                            &$modelId,
                             $srcDb,
                            &$isSerNumber){

    define('_IS_ACTIVE'     , 1);

    $query  = "SELECT SER_NUMBER
               FROM ".$srcDb."
               WHERE  MODEL_ID='".$modelId."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($result["SER_NUMBER"][0] == _IS_ACTIVE){

      $isSerNumber = true;
    }else{

      $isSerNumber = false;
    }
  }

  /**
   * fetch warehouse balance
   *
   * @param Database  $Db
   * @param integer   $modelId
   * @param string    $unit
   * @param string    $srcDb
   * @param integer   $curBalance
   */
  function fetchWhsBalance(&$Db,
                           &$modelId,
                           &$unit,
                            $srcDb,
                           &$nrows,
                           &$curBalance){

    $query  = "SELECT *
               FROM ".$srcDb."
               WHERE  MODEL_ID='".$modelId."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($nrows > 0){

      $curBalance = $result["QTY"][0];
    }else{

      $curBalance = 0;
    }
  }

  /**
   * last number of mutation
   *
   * @param Database  $Db
   * @param string    $refId
   * @param string    $srcDb
   * @param integer   $lastMutNumber
   */
  function fetchLastMutationNo(&$Db,
                               &$refId,
                                $srcDb,
                               &$lastMutNumber){

    $query  = "SELECT NO
               FROM ".$srcDb."
               WHERE  REFERENCE='".$refId."'
               ORDER BY NO DESC  ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($nrows > 0){

      $lastMutNumber = $result["NO"][0] + 1;
    }else{

      $lastMutNumber = 1;
    }
  }

  /**
   * general setting
   *
   * @param Database  $Db
   * @param string    $srcDb
   * @param array     $grlSetting
   */
  function fetchGrlSettingAll(&$Db,
                               $srcDb,
                              &$grlSetting){

    $query	= "SELECT *
               FROM ".$srcDb."  ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $grlSetting);
  }

  /**
  * fetch the list of valid transactions
  *
  * @param  string    $Db
  * @param  string    $condt      the condition
  * @param  string    $srcDb
  * @param  string    $lstResult
  *
  */
  function fetchLstTransaction(&$Db,
                                $condt,
                               &$srcDb,
                               &$nResult,
                               &$lstResult){

    $query  = "SELECT *
               FROM ".$srcDb."
               ".$condt."
               ORDER BY TR_DATE DESC ";

    $Db->execFetchQuery($query,
                        $nResult,
                        $lstResult);
  }

  /**
   * Tx Type Code by Name
   *
   * @param Database  $Db
   * @param string    $txName
   * @param string    $srcDb
   * @param string    $txCode
   */
  function fetchTxTypeCode($Db,
                           $txName,
                           $srcDb,
                           $txCode){

    $query  = "SELECT TX_CODE
               FROM ".$srcDb."
               WHERE  TX_NAME='".$txName."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $txCode = $result["TX_CODE"][0];
  }

  /**
   * general setting
   *
   * @param Database  $Db
   * @param string    $based
   * @param string    $srcDb
   * @param string    $return
   */
  function fetchGrlSetting(&$Db,
                            $based,
                            $srcDb,
                           &$return){

    $query	= "SELECT *
               FROM ".$srcDb." ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $return = $result[$based][0];
  }

  /**
   * fetch if the SN is valid
   *
   * @param Database  $Db
   * @param string    $chkSn
   * @param string    $srcDb
   * @param boolean   $isValid
   */
  function fetchIsSnValid(&$Db,
                          &$chkSn,
                           $srcDb,
                          &$isValid){

    $query	= "SELECT *
               FROM ".$srcDb."
               WHERE	SN='".$chkSn."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if ($nrows > 0) {

      $isValid = false;
    }else{

      $isValid = true;
    }
  }

  /**
   * list of suppliers
   *
   * @param Database $Db
   * @param string   $srcDb
   * @param array    $lstSupplier
   */
  function fetchSupplier(&$Db,
                          $srcDb,
                         &$lstSupplier) {

    $query	= "SELECT SUPPLIER_NAME
               FROM ".$srcDb."
               ORDER BY SUPPLIER_NAME ASC";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $lstSupplier = $result["SUPPLIER_NAME"];
  }


  /**
   * model Id
   *
   * @param Database $Db
   * @param string   $modelName
   * @param string   $srcDb
   * @param integer  $modelId
   */
  function fetchModelId(&$Db,
                        &$modelName,
                         $srcDb,
                        &$modelId){

    $query  = "SELECT MODEL_ID
               FROM ".$srcDb."
               WHERE  MODEL='".$modelName."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $modelId = $result["MODEL_ID"][0];
  }

  /**
  * Fetch login
  * Check if the user, pass and company is exist and active
  * return true  if the user is active and exists
  *        false if null return from the db fetch
  *
  * @param  string    $Db
  * @param  string    $user
  * @param  string    $pass
  * @param  string    $company
  * @param  string    $srcDb
  * @return booelan   true on success and false on failed
  *
  */
  function fetchLogin(&$Db,
                      &$user,
                      &$pass,
                      &$company,
                      &$srcDb){

    $query  = "SELECT USERID ".
              "FROM ".$srcDb." ".
              "WHERE  USERID='".$user."' ".
                      "AND PASSWORD='".$pass."' ".
                      "AND COMPANY='".$company."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);
    if($nrows > 0){

      return true;
    }else{

      return false;
    }
  }

  /**
  * Fetch currently selected levelD
  *
  * @param  string    $Db
  * @param  string    $srcDb
  * @param  string    $selected
  *
  */
  function fetchSelLevelD(&$Db,
                          &$srcDb,
                          &$selected){

    $query  = "SELECT LEVELD ".
              "FROM ".$srcDb." ".
              "WHERE  STATUS='"._ACTIVE."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $selected = $result["LEVELD"][0];
  }

  /**
  * fetch the last number of a table
  *
  * @param  string    $Db
  * @param  string    $today_date
  * @param  string    $srcDb
  * @param  string    $lastNo
  *
  */
  function fetchLastNoByDate(&$Db,
                             $today_date,
                             &$srcDb,
                             &$lastNo){

    $query  = "SELECT NO
               FROM ".$srcDb."
               WHERE  TR_DATE=TO_DATE('".$today_date."', 'dd-mm-YYYY')
               ORDER BY NO DESC ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    /**
    * Check if there is number in this date
    * YES: fetch it and increment
    * NO : Assign 1 as the first number
    *
    */
    if($nrows > 0){

      $lastNo = $result["NO"][0] + 1;
    }else{

      $lastNo = 1;
    }
  }

  /**
  * fetch the values data
  *
  * @param  string  $Db
  * @param  string  $userId
  * @param  string  $srcValues
  * @param  string  $srcDbHdr
  * @param  string  $srcDbDtl
  * @param  string  $theResult
  *
  */
  function fetchUnserialize(&$Db,
                            &$userId,
                            &$srcValues,
                            &$srcDbHdr,
                            &$srcDbDtl,
                            &$theResult){


  }

  /**
  * fetch the current number of sequence
  * of this sequence name
  *
  * @param  string  $Db
  * @param  string  $thisSequence
  * @param  string  $thisPeriode
  * @param  string  $srcDb
  * @param  string  $srcJidIndex
  *
  */
  function fetchCurSequence(&$Db,
            	              &$thisSequence,
            	              &$thisPeriode,
            	               $srcDb,
            	              &$srcJidIndex){

    $srcPeriode = "TO_DATE('".$thisPeriode."','mm-YYYY')";

    $query  = "SELECT SEQ_NUMBER
               FROM ".$srcDb."
               WHERE  SEQ_NAME='".$thisSequence."'
                      AND PERIODE=".$srcPeriode." ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $srcJidIndex = $result["SEQ_NUMBER"][0];
  }

  /**
  * fetch the last number of transaction
  * in this day
  *
  * @param  string    $Db
  * @param  string    $thisDate
  * @param  string    $srcDb
  * @param  string    $lastNumber
  *
  */
  function fetchHdrNo(&$Db,
                      &$thisDate,
                      &$srcDb,
                      &$lastNumber){

    $query  = "SELECT NO
               FROM ".$srcDb."
               WHERE  TR_DATE=TO_DATE('".$thisDate."','dd-mm-YYYY')
               ORDER BY NO DESC ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    /**
    * Return the result incremented
    *
    */
    $lastNumber = $result["NO"][0] + 1;
  }

  /**
  * fetch list of header data
  *
  * @param  string    $Db
  * @param  string    $jobId
  * @param  string    $srcDb
  * @param  integer   $nHeader
  * @param  string    $lstHeader
  *
  */
  function fetchLstDataHeader(&$Db,
                              &$jobId,
                              &$srcDb,
                              &$nHeader,
                              &$lstHeader){

    $query  = "SELECT *
               FROM ".$srcDb."
               WHERE  JOB_ID='".$jobId."' ";

    $Db->execFetchQuery($query,
                        $nHeader,
                        $lstHeader);
  }

  /**
  * fetch list of details data
  *
  * @param  string    $Db
  * @param  string    $jobId
  * @param  string    $srcDb
  * @param  integer   $nDetail
  * @param  string    $lstDetail
  *
  */
  function fetchLstDataDetail( &$Db,
                               &$jobId,
                               &$srcDb,
                               &$nDetail,
                               &$lstDetail){

    $query  = "SELECT *
               FROM ".$srcDb."
               WHERE  JOB_ID='".$jobId."'
               ORDER BY NO ASC ";

    $Db->execFetchQuery($query,
                        $nDetail,
                        $lstDetail);
  }

  /**
  * fetch list of sub details data
  *
  * @param  string    $Db
  * @param  string    $jobId
  * @param  string    $srcDb
  * @param  integer   $nSubDetail
  * @param  string    $lstSubDetail
  *
  */
  function fetchLstDataSubDetail(&$Db,
                                 &$srcDtlNo,
                                 &$jobId,
                                 &$srcDb,
                                 &$nSubDetail,
                                 &$lstSubDetail){

    $query  = "SELECT *
               FROM ".$srcDb."
               WHERE  JOB_ID='".$jobId."'
                      AND DTL_NO=".$srcDtlNo."
               ORDER BY NO ASC ";

    $Db->execFetchQuery($query,
                        $nSubDetail,
                        $lstSubDetail);
  }

  /**
  * fetch this periode sequence
  * to validate if this periode
  * isExists
  *
  * @param  string    $Db
  * @param  string    $jobId
  * @param  string    $srcDb
  * @param  integer   $nHeader
  * @param  string    $lstHeader
  *
  */
  function fetchThisPeriodeSequence( &$Db,
                                     &$thisPeriode,
                                     &$srcDb,
                                     &$isExists){

    $query  = "SELECT SEQ_NAME
               FROM ".$srcDb."
               WHERE  PERIODE=TO_DATE('".$thisPeriode."', 'mm-YYYY') ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($nrows > 0){

      $isExists = true;
    }else{

      $isExists = false;
    }
  }


  /**
  * Fetch all active sequence name by date
  *
  * @param  string    $Db
  * @param  date      $lastPeriode
  * @param  string    $srcDb
  * @param  string    $nSequence
  * @param  integer   $lstSeqName
  */
  function fetchSeqNameByDate( &$Db,
                               &$lastPeriode,
                               &$srcDb,
                               &$nSequence,
                               &$lstSeqName){

    $query  = "SELECT SEQ_NAME
               FROM ".$srcDb."
               WHERE  PERIODE=TO_DATE('".$lastPeriode."' ,'dd-mm-YYYY')";

    $Db->execFetchQuery($query,
                        $nSequence,
                        $result);

    $lstSeqName = $result["SEQ_NAME"];
  }

  /**
  * Fetch last periode of sequence
  *
  * @param  string  $Db
  * @param  string  $srcDb
  * @param  string  $lastPeriode
  *
  */
  function fetchLastPeriode( &$Db,
                             &$srcDb,
                             &$lastPeriode){

    $query  = "SELECT PERIODE
               FROM ".$srcDb."
               ORDER BY PERIODE DESC ";

    $Db->execFetchQuery($query,
                             $nrows,
                             $result);

    $lastPeriode = date("d-m-Y",
                        strtotime($result["PERIODE"][0]));
  }


  function fetchLastNumberByJobId(&$Db,
                                  &$jobId,
                                  &$srcDb,
                                  &$lastNumber){

    $query  = "SELECT NO
               FROM ".$srcDb."
               WHERE  JOB_ID='".$jobId."'
               ORDER BY NO DESC ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($nrows > 0){

      $lastNumber = (int) $result["NO"][0] + 1;
    }else{

      $lastNumber = 1; /* Default values since there is no entry */
    }
  }

  /**
  * fetch if seguence name is exists
  *
  * @param  string  $Db
  * @param  string  $Db
  * @param  string  $Db
  * @param  string  $Db
  * @param  string  $Db
  *
  */
  function fetchIsSeqExists( &$Db,
                             &$index,
                             &$entSequence,
                             &$srcDb,
                             &$isExists){

    $theDate = date("d-m-Y",
                    strtotime($entSequence["PERIODE"][$index]));

    $periode = " TO_DATE('".$theDate."','dd-mm-YYYY') ";

    $query  = "SELECT *
               FROM ".$srcDb."
               WHERE  SEQ_NAME='".$entSequence["SEQ_NAME"][$index]."'
                      AND PERIODE=".$periode." ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($nrows > 0){

      $isExists = true;
    }else{

      $isExists = false;
    }
  }

  /**
  * Fetch last number entry of this tx
  *
  */
  function fetchLastNumEntry(&$Db,
                             &$srcDb,
                             &$lastNumber){

    $query  = "SELECT NO
               FROM ".$srcDb."
               ORDER BY NO DESC";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($nrows > 0){

      $lastNumber = $result["NO"][0];
    }else{

      $lastNumber = 1; /* Default start of number */
    }
  }

  /**
  * fetch level MN which reflects the menu ID
  *
  * @param  string  $Db
  * @param  string  $srcModul
  * @param  string  $srcDb
  * @param  string  $menuId
  *
  */
  function fetchLevelMn(&$Db,
                        &$srcModul,
                        &$srcDb,
                        &$menuId){

    $query  = "SELECT LEVELMN
               FROM ".$srcDb."
               WHERE  DESCRIPTION='".$srcModul."' ";

    $Db->execFetchQuery($query,
                             $nrows,
                             $result);

    if($nrows > 0){

      $menuId = $result["LEVELMN"][0];
    }else{

      Misc::usrDieJs("Error: Unknown source modul ".$srcModul." ");
    }
  }

  /**
  * fetch the tocTab elements
  *
  * @param  string  $CDb
  * @param  string  $userId
  * @param  string  $companySel
  * @param  string  $srcDb
  * @param  string  $nrows
  * @param  string  $result
  *
  */
  function fetchTocTab(&$CDb,
                       &$userId,
                       &$companySel,
                       &$srcDb,
                       &$nrows,
                       &$result){

    $query  = "SELECT LEVELMN, ".
		          "       DESCRIPTION ".
		          "FROM ".$srcDb." ".
		          "WHERE  USERID='".$userId."' ".
		          "       AND COMPANY='".$companySel."' ".
		          "       AND STATUS=1 ".
		          "ORDER BY LEVELMN ASC";

    $CDb->execFetchQuery($query,
                         $nrows,
                         $result);
  }

  /**
  * fetch the user details
  *
  * @param  string  $CDb
  * @param  string  $usrId
  * @param  string  $compSel
  * @param  string  $srcDb
  * @param  string  $usrName
  * @param  string  $usrDeptName
  * @param  string  $usrPos
  *
  */
  function fetchUserDetail(&$CDb,
                           &$usrId,
                           &$compSel,
                           &$srcDb,
                           &$usrName,
                           &$usrDeptName,
                           &$usrPos){

     $query	=	"SELECT USERNAME,
		                  POS,
		                  DEPARTMENT
		           FROM ".$srcDb."
		           WHERE USERID='".$usrId."' ";

    $CDb->execFetchQuery($query,
                         $nrows,
                         $result);

    $usrName      = $result["USERNAME"][0];
    $usrDeptName  = $result["DEPARTMENT"][0];
    $usrPos       = $result["POS"][0];
  }

  /**
  * fetch the user details
  *
  * @param  string  $CDb
  * @param  string  $usrId
  * @param  string  $compSel
  * @param  string  $srcDb
  * @param  array   $privs
  * @param  array   $modulName
  *
  */
  function fetchUserPrivs(&$CDb,
                          &$usrId,
                          &$compSel,
                          &$srcDb,
                          &$privs,
                          &$modulName){

    /**
    * STATUS = 1 MEANS ACTIVE
    *
    */
    $query  = "SELECT *
							 FROM ".$srcDb."
							 WHERE  USERID='".$usrId."'
							        AND COMPANY='".$compSel."'
							        AND STATUS=1
							 ORDER BY LEVELMN ASC";

    $CDb->execFetchQuery($query,
                         $nrows,
                         $result);

    $privs["LEVELMN"]					=	$result["LEVELMN"];
		$privs["STATUS"]			    =	$result["STATUS"];
		$privs["ENTRY_RIGHTS"]		=	$result["ENTRY_RIGHTS"];
		$privs["EDIT_RIGHTS"]			=	$result["EDIT_RIGHTS"];
		$privs["DELETE_RIGHTS"]		=	$result["DELETE_RIGHTS"];
		$privs["VIEW_RIGHTS"]			=	$result["VIEW_RIGHTS"];
		$privs["FINALIZE_RIGHTS"]	=	$result["FINALIZE_RIGHTS"];
		$privs["APPROVED_RIGHTS"]	=	$result["APPROVED_RIGHTS"];
		$privs["JOURNAL_RIGHTS"]	=	$result["JOURNAL_RIGHTS"];
		$privs["PROGRAMS"]				=	$result["PROGRAMS"];
		$privs["PICT"]						=	$result["PICT"];

		$modulName = $result["DESCRIPTION"];
  }
}
?>